-- 1. partial backup, requires SQL Server Enterprise Edition
-- you can set the SECONDARY filegroup readonly

ALTER DATABASE Partitioning SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

ALTER DATABASE Partitioning 
MODIFY FILEGROUP [SECONDARY] READONLY;

ALTER DATABASE Partitioning SET MULTI_USER;
GO

-- a partial backup of the database is a full backup that contains READ_WRITE_FILEGROUPS only
-- in this demo the Primary filegroup only since the Secondary is readonly
BACKUP DATABASE Partitioning READ_WRITE_FILEGROUPS
TO DISK = 'C:\MSSQL\Backup\Partitioning.bak';
GO

-- look at the output window similar like this,
-- we see the Partitioning_dat and the Partitioning_log files only
/*
Processed 5520 pages for database 'Partitioning', file 'Partitioning_dat' on file 1.
Processed 2 pages for database 'Partitioning', file 'Partitioning_log' on file 1.
BACKUP DATABASE...FILE=<name> successfully processed 5522 pages in 0.786 seconds (54.880 MB/sec).
*/

-- now backup the Secondary filegroup
BACKUP DATABASE Partitioning
   FILEGROUP = 'SECONDARY'
   TO DISK = 'C:\MSSQL\Backup\Secondary.bak';
GO

-- set the SECONDARY filegroup to readwrite
ALTER DATABASE Partitioning SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

ALTER DATABASE Partitioning 
MODIFY FILEGROUP [SECONDARY] READWRITE;

ALTER DATABASE Partitioning SET MULTI_USER;
GO

USE Partitioning;
GO

-- add a record with orderdate > 2010-01-01 to the orders table,
-- it automatically goes into the PRIMARY partition
INSERT INTO dbo.Orders(orderid, custid, empid, shipperid, orderdate)
SELECT TOP 1
	orderid + 1 AS orderid, custid, empid, shipperid, orderdate
FROM dbo.Orders ORDER BY orderid DESC;

-- add a record with orderdate < 2010-01-01 to the orders table,
-- it automatically goes into the SECONDRY partition
-- raises an error when SECONDARY is read-only
INSERT INTO dbo.Orders(orderid, custid, empid, shipperid, orderdate)
SELECT TOP 1
	orderid + 1 AS orderid, custid, empid, shipperid, DATEADD(year, -4, orderdate) AS orderdate
FROM dbo.Orders ORDER BY orderid DESC;

-- (tail) log backup to catch the latest changes
BACKUP LOG Partitioning
TO DISK = 'C:\MSSQL\Backup\Partitioning.trn'; -- , NO_TRUNCATE
GO

-- 2. filegroup backup, does not require SQL Server Enterprise Edition
-- backup single filegroups
BACKUP DATABASE Partitioning
   FILEGROUP = 'PRIMARY'
   TO DISK = 'C:\MSSQL\Backup\Primary.bak';
GO

BACKUP DATABASE Partitioning
   FILEGROUP = 'SECONDARY'
   TO DISK = 'C:\MSSQL\Backup\Secondary.bak';
GO

-- (tail) log backup to catch the latest changes
BACKUP LOG Partitioning
TO DISK = 'C:\MSSQL\Backup\Log.trn'; -- , NO_TRUNCATE
GO